﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace _2021_6_24
{
    public class SqlAssistant
    {
        static SqlConnection con;
        /// <summary>
        /// 自定义链接的数据库，默认为学校机房的Student_db数据库
        /// </summary>
        /// <param name="serverAddress">服务器地址,默认为本机</param>
        /// <param name="uid">账号，默认为学校机房的账号</param>
        /// <param name="pwd">密码，默认为学校机房的密码</param>
        /// <param name="database">要链接的数据库，默认为本次作业的数据库</param>
        public SqlAssistant(string serverAddress = "." , string uid = "sa" , string pwd = "123456" , string database = "Student_db")
        {
            con = new SqlConnection($"server = {serverAddress} ; uid = {uid} ; pwd = {pwd} ; database = {database}");
        }

        public SqlAssistant( string database , string serverAddress)
        {
            con = new SqlConnection($"Data Source = {serverAddress} ; Initial Catalog = {database} ; Trusted_Connection = true");
        }
        /// <summary>
        /// 执行SQL非查询语句
        /// </summary>
        /// <param name="sql">需要执行的sql语句</param>
        /// <param name="pars">可选的参数</param>
        /// <returns>执行是否有返回记录</returns>
        public bool Execute(string sql , SqlParameter[] pars = null)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }


                SqlCommand cmd = new SqlCommand(sql,con);

                if (pars != null && pars.Length != 0)
                {
                    cmd.Parameters.AddRange(pars);
                }

                return cmd.ExecuteNonQuery() > 0;
        }
            catch(Exception e)
            {
                throw new Exception(e.Message);
    }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }

        }

        /// <summary>
        /// 执行数据库查询语句
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="pars">可选参数</param>
        /// <returns>数据表</returns>
        public DataTable Querty(string sql, SqlParameter[] pars)
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                
                
                SqlCommand cmd = new SqlCommand(sql,con);
                if (pars.Length != 0 && pars != null)
                {
                    cmd.Parameters.AddRange(pars);
                }
               
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);

                return ds.Tables[0];

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
        }

        
    }
}